/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package in.co.sneh.controller.FileUploadServlet;

import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;

/**
 *
 * @author Balrog
 */
public class ExportacionBD {
    MysqlDataSource datasource;
    Connection conexion;
    Statement st;
    String estado="Ok";
    int tamaño1=0;
    int tamaño2=0;
    
    
    public void exportacionBD(String ruta){
        datasource=new MysqlDataSource();
        datasource.setUser("root");
        datasource.setPassword("root");
        datasource.setDatabaseName("validador_tef");
        datasource.setServerName("127.0.0.1");
        try {
            conexion=datasource.getConnection();
            st=conexion.createStatement();
        } catch (SQLException ex) {
            estado=ex.getMessage();
                    } 

   ArrayList semanas = new ArrayList();
    Integer semana;
    Integer semana_anterior = -1;
    Integer anio;
    int cont = 0;
    String FechaVuelo;
    Integer mes;
    Diccionarios dic = new Diccionarios();
    Integer dia;
    FileWriter fw;
    FileWriter fw2 = null;
    FileWriter fw3 = null;
    PrintWriter fw4 = null;
    BufferedWriter out1 = null;
    BufferedWriter out2 = null;

    String directorio_expedientes = ruta;
    String ruta_archivo_expedientes;
    File carpeta_expedientes = new File(directorio_expedientes);
    String[] lista = carpeta_expedientes.list();
    StringBuilder semana_años = new StringBuilder();
    String semana_año = null;
        for (int z = 0; z < lista.length; z++) {
            ruta_archivo_expedientes = directorio_expedientes + "\\" + lista[z];
            String[] nombre_archivo = ruta_archivo_expedientes.split("-");
            semana_años.append(nombre_archivo[1] + "-" + nombre_archivo[2].substring(0, nombre_archivo[2].length() - 4));
            if (z != lista.length - 1) {
                semana_años.append(",");
            }
        }

    try {
        Statement st = conexion.createStatement();
        //this query gets all the tables in your database(put your db name in the query)
        ResultSet res = null;
          //Preparing List of table Names
          //star iterating on each table to fetch its data and save in a .csv file
        int k = 0;
        int j = 1;
        List<String> columnsNameList = new ArrayList<String>();
        //select all data from table
        res = st.executeQuery("select * from newexp");
        //colunm count is necessay as the tables are dynamic and we need to figure out the numbers of columns
        int colunmCount = getColumnCount(res);

        try {
           


            while (res.next()) {
                for (int i = 1; i <= colunmCount - 2; i++) {

                    //you can update it here by using the column type but i am fine with the data so just converting 
                    //everything to string first and then saving
                    if (res.getObject(i) != null) {
                        String data = res.getObject(i).toString();
                        if (i == 1) {
                            String[] fechas = data.split("-");
                            semana = numero_de_semana(data);
                            semana_año = semana + "-" + fechas[0].substring(2, 4);
                            if (!semana_años.toString().contains(semana_año)) {
                                fw2 = new FileWriter(directorio_expedientes + "\\" + "Informe_TEF_Semana-" + semana + "-" + fechas[0].substring(2, 4) + ".csv");
                                for (int w = 1; w <= colunmCount - 2; w++) {
                                    fw2.append(res.getMetaData().getColumnName(w).toLowerCase());
                                    if (w != colunmCount - 2) {
                                        fw2.append(";");
                                    }
                                }
                                fw2.append(System.getProperty("line.separator"));
                                fw2.flush();
                                fw2.close();
                                if (semana_años.length() == 0) {
                                    semana_años.append(semana_año);
                                } else {
                                    semana_años.append("," + semana_año);
                                }
                            }
                        }
                        try {
                            fw4 = new PrintWriter(new BufferedWriter(new FileWriter(directorio_expedientes + "\\" + "Informe_TEF_Semana-" + semana_año + ".csv", true)));
                            fw4.append(data);
                        } catch (IOException e) {
                            //exception handling left as an exercise for the reader
                        }
                        if (i != colunmCount - 2) {
                            fw4.append(";");
                            fw4.flush();
                            fw4.close();
                        }
                    } else {
                        String data = "null";
                        fw4.append(";");
                        fw4.flush();
                        fw4.close();
                    }

                }
                //new line entered after each row
                fw4.append(System.getProperty("line.separator"));
                fw4.flush();
                fw4.close();
            }

            fw4.flush();
            fw4.close();

        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        conexion.close();
    } catch (SQLException ex) {
        System.err.println("SQLException information");
    }
    }
    
public String cambiar_dia(String dia) {
            
                switch (dia) {
                    case "1":
                        dia = "lunes";
                        break;
                    case "2":
                        dia = "martes";
                        break;
                    case "3":
                        dia = "miercoles";
                        break;
                    case "4":
                        dia = "jueves";
                        break;
                    case "5":
                        dia = "viernes";
                        break;
                    case "6":
                        dia = "sabado";
                        break;
                    case "7":
                        dia = "domingo";
                        break;
                    
                }
                        


        return dia;
    }
  public static int  getColumnCount(ResultSet res) throws SQLException
    {
        return res.getMetaData().getColumnCount();
    }
  
  public static int numero_de_semana(String fecha){
    String [] fechas=fecha.split("-");
 int anio=Integer.parseInt(fechas[0]);
     int        mes=Integer.parseInt(fechas[1]);
         int   dia=Integer.parseInt(fechas[2]);
  Calendar cumpleCal = Calendar.getInstance();
            cumpleCal.set(anio,mes-1,dia); //La hora no me interesa y recuerda que los meses van de 0 a 11
      int      semana=cumpleCal.get(Calendar.WEEK_OF_YEAR);
  return semana;
  }

    

        
        
        
        
    }
    
    
   
    
    
    
    
    
    
  
  
